﻿using System;
using System.Data;
using System.Data.SqlClient;
using bank.DTOs;
namespace bank.DataAccess
{
    public class Query
    {
        SqlConnection conn;
        SqlCommand cmd;
        SqlDataAdapter dataAdapter;
        DataSet ds;
        public Query()
        {
            conn = new SqlConnection();
            conn.ConnectionString = "server=localhost;Database=SchoolDb;user id=sa;password=sa123";
        }
        public bool InsertStudentData(StudentInformation student)
        {
            cmd = new SqlCommand();
            cmd.Connection = conn;
            //cmd.CommandText = "insert into student values(111,'arun','A23')";
            cmd.CommandText = "insert into student values(" + student.RollNo + ",'" + student.Name + "'," + student.Age + ")";
            try
            {
                conn.Open(); // connect to DB
                cmd.ExecuteNonQuery(); //insert, update and delete
                //conn.Close(); // discconect from DB
                return true;
            }
            catch (Exception exp)
            {
                return false;
            }
            finally
            {
                conn.Close(); // discconect from DB
            }
        }

        public bool AddStudentData(StudentInformation student)
        {
            cmd = new SqlCommand();
            cmd.Connection = conn;
            //cmd.CommandText = "insert into student(name,roll,age) values('arun',111,'A23')";  add(int n1, int n2)  / add(10,80)
            cmd.CommandText = "insert into student values(@RollNo,@Name,@Age)";
            cmd.Parameters.Add("@RollNo", SqlDbType.BigInt, 4);
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100);
            cmd.Parameters.Add("@Age", SqlDbType.Int, 3);

            cmd.Parameters["@RollNo"].Value = student.RollNo;
            cmd.Parameters["@Name"].Value = student.Name;
            cmd.Parameters["@Age"].Value = student.Age;

            try
            {
                conn.Open(); // connect to DB
                cmd.ExecuteNonQuery(); //insert, update and delete
                //conn.Close(); // discconect from DB
                return true;
            }
            catch (Exception exp)
            {
                return false;
            }
            finally
            {
                conn.Close(); // discconect from DB
            }
        }

    }
}
